﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;

/// <summary>
/// Summary description for DAL_BenhNhan
/// </summary>
public class DAL_BenhNhan : SqlConnectionRun
{
    private List<SqlParameter> parameters = null;
    public DAL_BenhNhan()
    {

    }

    public List<DS_ChoKham> ViewDanhSachChoKham(string _MaKhoa, string _MaBN, string _HoTen, string _TuNgay, string _DenNgay, bool ChoDonThuoc)
    {
        List<DS_ChoKham> lstDSBenhNhanCho = new List<DS_ChoKham>();
        parameters = new List<SqlParameter>();
        string strselect = @"SELECT  MaBN, MaPhieu, Hoten, cast(Namsinh as varchar(4)) namsinh, Ngay, makhoa, TenDichVu,  Madotkham, STT, Ketthuc, Uutien, IDCT, GioiTinh, SoBHYT, HanBH,                               
                                case DoiTuong when 1 then 'BHYT' when 2 then 'DV' when 5 then 'TE' end AS DoiTuong, 
                                case SuDung when 0 then N'Chờ khám' when 1 then N'Chờ kê toa' end Sudung,
                                case isnull(Loai_Kham_Cp,0) when '0' then N'Khám chính' else N'Khám hội chẩn' end as loaikham
                                FROM    phongkham..DS_ChoKham
                                WHERE   makhoa=N'" + _MaKhoa + "' and KetThuc=0 and Ketoa='" + ChoDonThuoc.ToString() + "'";
        parameters.Add(new SqlParameter("MaKhoa", _MaKhoa));
        parameters.Add(new SqlParameter("KetThuc", 0));
        SqlParameter paramStatusKeToa = new SqlParameter("KeToa", SqlDbType.NVarChar, 20);
        paramStatusKeToa.Value = ChoDonThuoc;
        parameters.Add(paramStatusKeToa);
        if (_HoTen != "")
        {
            strselect += " and hoten like N'%" + _HoTen.Trim() + "%'";
        }

        if (_MaBN != "")
        {
            strselect += " and mabn LIKE '%" + _MaBN.Trim() + "'";
        }

        if (_TuNgay != "" && _DenNgay != "")
        {
            if (_TuNgay == _DenNgay)
            {
                strselect += " and Ngay between CONVERT(DATETIME,'" + _TuNgay + " 00:00', 103) and CONVERT(DATETIME,'" + _DenNgay + " 23:59', 103) ";
            }
            else
            {
                strselect += " and CONVERT(DATE, Ngay) between CONVERT(DATE,'" + _TuNgay + "', 103) and CONVERT(DATE,'" + _DenNgay + "', 103) ";
            }
        }
        else
        {
            if (_TuNgay != "")
            {
                strselect += " and Ngay > CONVERT(DATETIME,'" + _TuNgay + " 00:00', 103) ";
            }
            if (_DenNgay != "")
            {
                strselect += " and Ngay < CONVERT(DATETIME,'" + _DenNgay + " 23:59', 103) ";
            }
        }
        strselect += " order by STT";
        DataTable tempTable = SqlExecuteQueryReader(strselect);
        DS_ChoKham BenhNhanCho = null;
        foreach (DataRow row in tempTable.Rows)
        {
            BenhNhanCho = new DS_ChoKham();
            BenhNhanCho.IDCT = row["IDCT"].ToString();
            BenhNhanCho.STT = int.Parse(row["STT"].ToString());
            BenhNhanCho.MaBN = row["MaBN"].ToString().Trim();
            BenhNhanCho.MaPhieu = row["MaPhieu"].ToString().Trim();
            BenhNhanCho.MaKhoa = row["MaKhoa"].ToString().Trim();
            BenhNhanCho.HoTen = row["HoTen"].ToString();
            BenhNhanCho.GioiTinh = row["GioiTinh"].ToString();
            if (Factory.emptyNull(row["NamSinh"]) != "")
                BenhNhanCho.NamSinh = int.Parse(row["NamSinh"].ToString());
            BenhNhanCho.TenDichVu = row["TenDichVu"].ToString().Trim();
            BenhNhanCho.LoaiKham = row["LoaiKham"].ToString();
            BenhNhanCho.DoiTuong = row["DoiTuong"].ToString();
            BenhNhanCho.UuTien = Boolean.Parse(row["UuTien"].ToString());
            BenhNhanCho.NgayDangKy = DateTime.Parse(row["Ngay"].ToString());
            BenhNhanCho.TrangThai = row["SuDung"].ToString();
            BenhNhanCho.SoBHYT = row["SoBHYT"].ToString();
            if (Factory.emptyNull(row["HanBH"]) != "")
                BenhNhanCho.HanBH = DateTime.Parse(row["HanBH"].ToString());
            BenhNhanCho.MaDotKham = row["MaDotKham"].ToString();
            lstDSBenhNhanCho.Add(BenhNhanCho);
        }
        return lstDSBenhNhanCho;
    }

    public DataTable LayDanhSachChoKham(string MaKhoa, string HoTen, string MaBN, string TuNgay, string DenNgay)
    {
        string FromDate = Factory.ConvertDateTime(TuNgay);
        string ToDate = Factory.ConvertDateTime(DenNgay);
        if (Factory.IsDate(FromDate) == false)
        {
            FromDate = "null";
        }
        else
        {
            FromDate = "'" + FromDate + "'";
        }
        if (Factory.IsDate(ToDate) == false)
        {
            ToDate = "null";
        }
        else
        {
            ToDate = "'" + ToDate + "'";
        }
        if (MaBN == "")
        {
            MaBN = "null";
        }
        else
        {
            MaBN = "'" + MaBN + "'";
        }
        if (HoTen == "")
        {
            HoTen = "null";
        }
        else
        {
            HoTen = "N'%" + HoTen + "%'";
        }
        string SQL = "EXEC dbo.sp_DanhSachBenhNhanCho " +
                     FromDate + "," +
                     ToDate + "," +
                     "'" + MaKhoa + "'" + "," +
                     MaBN + "," +
                     HoTen;
        DataTable dt = SqlExecuteQueryReader(SQL);
        return dt;
    }

    public DataTable LayThongTinSinhHieu(string MaBN)
    {
        string strselect = "SINHHIEU..sp_sinhhieu";
        List<SqlParameter> lst = new List<SqlParameter>();
        lst.Add(new SqlParameter("@MaBN", MaBN));
        DataTable dt = SqlRunStoredProcedure(strselect, lst);
        return dt;
    }

    public BenhNhan LayThongTinBenhNhan(string MaBN)
    {
        string sql = @"SELECT * FROM NghiepVuY..BangTiepNhan WHERE MaBN='" + MaBN + "'";
        DataTable dt = SqlExecuteQueryReader(sql);
        BenhNhan BenhNhan = new BenhNhan();
        foreach (DataRow row in dt.Rows)
        {
            BenhNhan.MaBN = row["MaBN"].ToString();
            BenhNhan.HoTen = row["HoTen"].ToString();
            BenhNhan.DiaChi = row["DiaChi"].ToString();
            BenhNhan.DoiTuong = row["DoiTuong"].ToString();
            BenhNhan.GioiTinh = row["GioiTinh"].ToString();
            if (Factory.emptyNull(row["NgaySinh"]) != "")
                BenhNhan.NgaySinh = DateTime.Parse(row["NgaySinh"].ToString());
            BenhNhan.NamSinh = row["NamSinh"].ToString();
        }
        return BenhNhan;
    }

    public string LayThongTinDiaChi(string MaBN)
    {
        string sqlselect = "select p.TENPXA,q.TENQUAN,t.TENTT from NghiepVuY..BangTiepNhan as b " +
                            " left outer join NghiepVuY..BTDPXA as p on b.XaPhuong=p.MaPhuongXa " +
                            " left outer join NghiepVuY..BTDQUAN as q on b.HuyenQuan=q.MAQU " +
                            " left outer join NghiepVuY..BTDTT as t on b.TinhThanh=t.MATT " +
                            " where b.MaBN like N'%" + MaBN + "%'";
        DataTable dt = SqlExecuteQueryReader(sqlselect);
        DataRow row = dt.Rows[0];
        string diachi = "";
        if (row["TENPXA"].ToString().Trim() != "" && row["TENPXA"].ToString().Trim().ToLower() != "không xác định")
        { diachi += " - "; }
        if (row["TENPXA"].ToString().Trim().ToLower() != "không xác định") { diachi += row["TENPXA"].ToString(); }
        if (diachi.Trim() != "" && row["TENQUAN"].ToString().Trim() != "" && row["TENQUAN"].ToString().Trim().ToLower() != "không xác định")
        { diachi += " - "; }
        if (row["TENQUAN"].ToString().Trim().ToLower() != "không xác định") { diachi += row["TENQUAN"].ToString(); }
        if (diachi.Trim() != "" && row["TENTT"].ToString().Trim() != "" && row["TENTT"].ToString().Trim().ToLower() != "không xác định")
        { diachi += " - "; }
        if (row["TENTT"].ToString().Trim().ToLower() != "không xác định") { diachi += row["TENTT"].ToString(); }
        return diachi;
    }

    /// <summary>
    /// Kiểm tra xem bn thuong hay BH
    /// </summary>
    /// 
    public int getLoaiBN(string sMabn)
    {
        string store = "NghiepVuY..usp_getDoituong";
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@mabn", sMabn));
        int nRetval = 0;
        try
        {
            object obj = SqlRunStoredProcedureScalar(store, parameters);
            nRetval = Convert.ToInt32(obj);
        }
        catch (Exception)
        {

        }
        return nRetval;
    }

    public string TaoMaBenhNhan(string tenphieu)
    {        
        string tmaphieu = tenphieu;
        string ngay = DateTime.Today.ToShortDateString();
        string nm = DateTime.Today.Year.ToString();
        nm = nm.Substring(nm.Length - 2);
        string sqlmaphieu = "Select * from PhongKham..BangDemMaSo where ten='" + tmaphieu + "' and kyhieu='" + nm + "'";
        DataTable dt = SqlExecuteQueryReader(sqlmaphieu);
        int th;
        string tthang = "";
        int ng;
        string tngay = "";
        string maso = "";
        int dai;
        int tongso = 0;
        if (dt.Rows.Count <= 0)
        {
            string sqlinsertmp = "insert into PhongKham..BangDemMaSo(Ten,KyHieu,TongSo) values('" + tmaphieu + "','" + nm + "',0)";
            SqlExecuteNoneQuery(sqlinsertmp);
            maso = "00000";
            tongso = 1;
        }
        else
        {

            tongso = int.Parse(dt.Rows[0]["TongSo"].ToString()) + 1;
            dai = tongso.ToString().Length;
            dai = 6 - dai;
            for (int i = 1; i <= dai; i++)
            {
                maso = maso + "0";
            }
        }
        th = DateTime.Today.Month.ToString().Length;
        th = 2 - th;
        for (int i = 1; i <= th; i++)
        {
            tthang = tthang + "0";
        }
        tthang = tthang + DateTime.Today.Month.ToString();
        ng = DateTime.Today.Day.ToString().Length;
        ng = 2 - ng;
        for (int i = 1; i <= ng; i++)
        {
            tngay = tngay + "0";
        }
        tngay = tngay + DateTime.Today.Day.ToString();
        //''''''''''''''''
        maso = maso + tongso.ToString();
        tmaphieu = "BNPK" + nm + maso;      
        return tmaphieu;
    }

    public bool LuuThongTinBenhNhanPhongKham(string MaBN, string HoTen, string GioiTinh, string DiaChi, int Tuoi)
    {                       
        string sqlinsert = "Insert into PhongKham..benhnhan(MaBN,HoTen,GioiTinh,NamSinh,DiaChi) values(N'" +
        MaBN + "',N'" + HoTen + "'," + GioiTinh + "," + Tuoi + ",N'" + DiaChi + "')";        
        int result = SqlExecuteNoneQuery(sqlinsert);
        return result > 0;
    }
}